

'''
-- 创建用户表
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT SETVAL('users_user_id_seq',max(user_id),TRUE) FROM users;
'''

from . import logger

class UsersTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor


    # 创建用户
    def create_user(self, user_name, phone, password):
        
        try:
            self.cursor.execute("INSERT INTO users (user_name, phone, password) VALUES (%s, %s, %s) RETURNING user_id", (user_name, phone, password))

        except Exception as e:
            logger.error("Error creating user: {}".format(e))
            return False, str(e)

        else:
            # self.connection.commit()  # 不需要提交事务，因为在使用postgresql_connection.py中的PostgreSQLConnection类时，with语句中已经提交了事务
            logger.info(f"User {user_name} created successfully.")
            return True, self.cursor.fetchone()["user_id"]


    # 根据用户名id获取用户信息
    def get_user_by_id(self, user_id):
    
        try:
            self.cursor.execute("SELECT * FROM users WHERE user_id = %s", (user_id,))
            user = self.cursor.fetchone()

        except Exception as e:
            logger.error("Error getting user by id: {}".format(e))
            user = None

        else:
            logger.info(f"User {user_id} retrieved successfully.")

        return user
    

    # 根据用户名获取用户信息
    def get_user_by_name(self, user_name):
        
        try:
            self.cursor.execute("SELECT * FROM users WHERE user_name = %s", (user_name,))
            user = self.cursor.fetchone()

        except Exception as e:
            logger.error("Error getting user by name: {}".format(e))
            user = None

        else:
            logger.info(f"User {user_name} retrieved successfully.")

        return user
    

    # 根据手机号获取用户信息 也可用来 验证手机号是否已注册
    def get_user_by_phone(self, phone):
        
        try:
            self.cursor.execute("SELECT * FROM users WHERE phone = %s", (phone,))
            user = self.cursor.fetchone()
            return user

        except Exception as e:
            logger.error("Error getting user by phone: {}".format(e))
            return None

    

    # 更新用户信息
    def update_user(self, user_id, user_name, phone, password):
        
        try:
            self.cursor.execute("UPDATE users SET user_name = %s, phone = %s, password = %s WHERE user_id = %s", (user_name, phone, password, user_id))

        except Exception as e:
            logger.error("Error updating user: {}".format(e))

        else:
            self.connection.commit()
            logger.info(f"User {user_id} updated successfully.")


    # 删除用户
    def delete_user(self, user_id):
        
        try:
            self.cursor.execute("DELETE FROM users WHERE user_id = %s", (user_id,))

        except Exception as e:
            logger.error("Error deleting user: {}".format(e))

        else:
            logger.info(f"User {user_id} deleted successfully.")


    # 获取所有用户信息
    def get_all_users(self, offset=0, limit=10):
        
        try:
            self.cursor.execute("SELECT * FROM users ORDER BY user_id DESC LIMIT %s OFFSET %s", (limit, offset))
            users = self.cursor.fetchall()

        except Exception as e:
            logger.error("Error getting all users: {}".format(e))
            users = []

        else:
            logger.info(f"All users retrieved successfully.")

        return users
    

    # 获取用户数量
    def get_user_count(self):
        
        try:
            self.cursor.execute("SELECT COUNT(*) AS count FROM users")
            count = self.cursor.fetchone()["count"]

        except Exception as e:
            logger.error("Error getting user count: {}".format(e))
            count = 0

        else:
            logger.info(f"User count retrieved successfully.")

        return count




    


    